﻿//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//  LIBRERIA: SIMA.CORE                                                                                                 //
//  DESARROLLADO POR JUAN CARLOS VEGA NEIRA                                                                             //
//  UNIVERSIDAD RICARDO PALMA                                                                                           //
//  CURSO DE TALLER DE GERENTCIA DE PROYECTOS                                                                           //
//  SEMESTRE 2010 - II                                                                                                  //
//  LIMA - PERÚ                                                                                                         //
//  SEPTIEMBRE, 2010                                                                                                    //
//  TODOS LOS DERECHOS RESERVADOS                                                                                       //
//  VERSIÓN v1.0                                                                                                        //
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using System.Threading;

using SIMA.Core.Data.Access;
using SIMA.Core.Utils;
using SIMA.Core.Exceptions;
using SIMA.Core.EventArgs;
using SIMA.Core.Enums;
using SIMA.Core.Attributes;
using SIMA.Core.Utils.Security;
using SIMA.Core.Extension;
using SIMA.Core.Data.Entity;
using SIMA.Core.Security;

namespace SIMA.Core.Data.Collection
{

    /// <summary>
    /// A collection that give a powerfull access to Database to set or get information rapidly. 
    /// First you need to set the connection on DataAccess class.
    /// </summary>
    /// <typeparam name="T">
    /// T is the type of entity you want to make changes
    /// </typeparam>

    #region Atributtes

    [Serializable]

    #endregion

    public class SmartContainer<T>:IList<T>,IEnumerator<T>,IDisposable where T:XmlEntity
    {

        #region Consts

        private const string S_DELIMITER = ",";
        private const string S_EXECUTEMESSAGE = "EJECUTADO";
        private const string S_INSERTMESSAGE = "AGREGADO";
        private const string S_UPDATEMESSAGE = "ACTUALIZADO";
        private const string S_DELETEMESSAGE = "ELIMINADO";
        private const string S_FAILEDMESSAGE = "ERROR";
        private const string S_FILEEXTENTION = ".xml";

        #endregion

        #region Fields

        private Node<T> beginNode = default(Node<T>);
        private T current;
        private int count = 0;
        private int index = -1;
        private bool isReadOnly = false;
        private string connectionString = string.Empty;
        private string path = string.Empty;
        private bool haveLog = false;
	    private bool hasConnectionOpen = false;
        private string filename = string.Empty;

        private SqlConnection sqlConnection;
        private SqlCommand sqlCommand;
        private SqlDataAdapter sqlAdapter;

        #endregion

        #region Properties

        /// <summary>
        /// Get the number of itmes in the container.
        /// </summary>
        public int Count
        {
            get { return this.Counter(beginNode, 0); }
        }

        /// <summary>
        /// Get or set the item on referred index.
        /// </summary>
        /// <param name="index">
        /// The position of item.
        /// </param>
        /// <returns>
        /// Item looking for.
        /// </returns>
        public T this[int index]
        {
            get
            {
                return GetNodeFromIndex(beginNode, 0, index).Data;
            }
            set
            {
                InsertIntoIndex(beginNode, null, value, 0, index);
            }
        }

        /// <summary>
        /// Get a list of items conditioned.
        /// </summary>
        /// <param name="queryExtension">
        /// Is the condition on SQL query.
        /// </param>
        /// <returns>
        /// List of items.
        /// </returns>
        public List<T> this[string queryExtension,CommandType type]
        {
            get
            {
                return SelectSpecilized(queryExtension,type);
            }
        }

        /// <summary>
        /// Get a boolean if the container is writeable.
        /// </summary>
        public bool IsReadOnly
        {
            get 
            { 
                return isReadOnly; 
            }
        }

        #endregion

        #region Events

        /// <summary>
        /// Event raise after updated an item of the table.
        /// </summary>
        public event EventHandler<UpdatedEventArgs> Updated;

        /// <summary>
        /// Event raise after deleted an item of the table.
        /// </summary>
        public event EventHandler<DeletedEventArgs> Deleted;

        /// <summary>
        /// Event raise after register an item of the table.
        /// </summary>
        public event EventHandler<InsertedEventArgs> Inserted;

        /// <summary>
        /// Event raise when an exception is catched.
        /// </summary>
        public event EventHandler<FailedEventArgs> Failed;

        /// <summary>
        /// Event raise when the collection changed.
        /// </summary>
        public event EventHandler CollectionChanged;

        #endregion

        #region Constructor

        /// <summary>
        /// Constructor Method to initialize the SmartContainer with default values.
        /// </summary>
        public SmartContainer()
        {
            Initializer();
        }

        /// <summary>
        /// Constructor Method to initialize the SmartContainer.
        /// </summary>
        /// <param name="isReadOnly">
        /// Parameter to assigned if you an edit the data content or just retreieve data.
        /// </param>
        public SmartContainer(bool isReadOnly)
        {
            Initializer();
            this.isReadOnly = isReadOnly;
        }

        /// <summary>
        /// Constructor Method to initialize the SmartContainer.
        /// </summary>
        /// <param name="isReadOnly">
        /// Parameter to assigned if you an edit the data content or just retreieve data.
        /// </param>
        /// <param name="isArchivedLog">
        /// Parameter to create a LogFile.
        /// </param>
        public SmartContainer(bool isReadOnly, bool isArchivedLog)
        {
            
            this.isReadOnly = isReadOnly;
            this.haveLog = isArchivedLog;
            Initializer();
        }

        #endregion	

	    #region Destructor

	    ~SmartContainer()
	    {
	        Dispose();
	    }

	    #endregion

        #region Class Helper

        [Serializable]
        public class Node<T>
        {
            #region Fields

            private T data;
            private Node<T> next;

            #endregion

            #region Properties

            public T Data
            {
                get
                {
                    return data;
                }
                set
                {
                    data = value;
                }
            }

            public Node<T> Next
            {
                get
                {
                    return next;
                }
                set
                {
                    next = value;
                }
            }

            #endregion
        }

        #endregion
        
        #region Private Methods

        #region Aux

        private void Initializer()
        {
            index = 0;
            
            connectionString = DataAccess.GetConnectionString();
            sqlConnection = new SqlConnection(connectionString);
	        hasConnectionOpen = false;
            beginNode = Activator.CreateInstance<Node<T>>();
            this.count = this.Counter(beginNode, index);
            this.current = GetNodeFromIndex(beginNode, 0, index).Data;
            if (haveLog)
            {
                StringBuilder strBuilder = new StringBuilder();
                strBuilder.Append(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments));
                strBuilder.Append("\\");
                strBuilder.Append(DateTime.Now.ToLongDateString());
                strBuilder.Append(S_FILEEXTENTION);
                filename = strBuilder.ToString();
            }
        }

        private bool SelectAllFromSQL()
        {
            string query = string.Empty;
            try
            {
                query = SqlUtils<T>.SelectAllQueryFormater();
                sqlAdapter = new SqlDataAdapter(query, sqlConnection);
                Type _type = typeof(T);

                sqlConnection.Open();
		        hasConnectionOpen = true;
                SqlDataReader reader = sqlAdapter.SelectCommand.ExecuteReader();

                AddToContainerFromSQL(beginNode, null, _type, reader);

		        sqlConnection.Close();
		        hasConnectionOpen = false;

                if (haveLog)
                {
                    Archive(query, Operations.SelectAll, null);
                }
                return true;

            }
            catch (CoreFaultException ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.SelectAll, ex);
                }
                if (Failed != null)
                {
                    Failed(this, new FailedEventArgs(query, ex, Operations.SelectAll));
                }
                return false;
            }
            catch (Exception ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.SelectAll, ex);
                }
                if (Failed != null)
                {
                    Failed(this, new FailedEventArgs(query, ex, Operations.SelectAll));
                }
                return false;
            }
            finally
            {
                if (hasConnectionOpen)
                {
                    sqlConnection.Close();
                    hasConnectionOpen = false;
                }
            }

        }

        private List<T> SelectSpecilized(string queryclause,CommandType type)
        {
            List<T> filtered = new List<T>();
            string query = string.Empty;
            try
            {
                if (type == CommandType.CommandText)
                {
                    if (queryclause == string.Empty)
                    {
                        throw new CoreFaultException();
                    }
                    query = SqlUtils<T>.SelectByQueryFormater(queryclause);

                    sqlAdapter = new SqlDataAdapter(query, sqlConnection);
                    Type _type = typeof(T);

                    sqlConnection.Open();
                    hasConnectionOpen = true;

                    SqlDataReader reader = sqlAdapter.SelectCommand.ExecuteReader();

                    return ApplyAdvanceFilter(beginNode, null, _type, reader, filtered, query);
                }
                else
                {
                    if (queryclause == string.Empty)
                    {
                        throw new CoreFaultException();
                    }
                    sqlAdapter = new SqlDataAdapter(queryclause, sqlConnection);
                    sqlAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    Type _type = typeof(T);

                    sqlConnection.Open();
                    hasConnectionOpen = true;
                    SqlDataReader reader = sqlAdapter.SelectCommand.ExecuteReader();
                    return ApplyAdvanceFilter(beginNode, null, _type, reader, filtered, query);
                }

            }
            catch (CoreFaultException ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.Select, ex);
                }
                if (Failed != null)
                {
                    Failed(this, new FailedEventArgs(query, ex, Operations.Select));
                }
                return new List<T>();
            }
            catch (Exception ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.Select, ex);
                }
                if (Failed != null)
                {
                    Failed(this, new FailedEventArgs(query, ex, Operations.Select));
                }
                return new List<T>();
            }
            finally
            {
                if (hasConnectionOpen)
                {
                    sqlConnection.Close();
                    hasConnectionOpen = false;
                }

            }
        }

        public List<T> ToList()
        {
            List<T> TList = new List<T>();
            TList = AddToList(beginNode, TList);
            return TList;
        }

        private bool AddToSQL(Node<T> BeginNode, T item)
        {
            string query = string.Empty;
            
            bool b = false;
            try
            {
                if (isReadOnly)
                {
                    throw new IsReadOnlyException();
                }
                else
                {
                    query = SqlUtils<T>.InsertQueryFormater(item);
                    sqlCommand = new SqlCommand(query, sqlConnection);
                    sqlConnection.Open();
                    hasConnectionOpen = true;
                    sqlCommand.ExecuteNonQuery();
                    if (hasConnectionOpen)
                    {
                        sqlConnection.Close();
                    }
                    Node<T> lastNode = Do(BeginNode);
                    Node<T> temp = Activator.CreateInstance<Node<T>>();
                    temp.Data = item;
                    temp.Next = null;
                    lastNode.Next = temp;
                    
                    if (haveLog)
                    {
                        Archive(query, Operations.Insert,null);
                    }
                    if (Inserted != null)
                    {
                        Inserted(this, new InsertedEventArgs(query, Operations.Insert));
                    }
                    
                    return true;
                }
            }
            catch (IsReadOnlyException ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.Insert, ex);
                }
                if (Failed != null)
                {
                    Failed(this, new FailedEventArgs(query, ex, Operations.Insert));
                }
                return false;
            }
            catch (ValidationException ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.Insert, ex);
                }
                if (Failed != null)
                {
                    Failed(this, new FailedEventArgs(query, ex, Operations.Insert));
                }
                return false;
            }
            catch (CoreFaultException ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.Insert, ex);
                }
                if (Failed != null)
                {
                    Failed(this, new FailedEventArgs(query, ex, Operations.Insert));
                }
                return false;
            }
            catch (Exception ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.Insert, ex);
                }
                if (Failed != null)
                {
                    Failed(this, new FailedEventArgs(query, ex, Operations.Insert));
                }
                return b;
            }
            finally
            {
                if (hasConnectionOpen)
                {
                    sqlConnection.Close();
                    hasConnectionOpen = false;
                }
                if (CollectionChanged != null)
                {
                    CollectionChanged(this, new System.EventArgs());
                }
            }
        }

        private bool UpdateToSQL(T itemToUpdate, T item)
        {
            string query = string.Empty;
            bool result = false;
            T itm = Exist(beginNode, itemToUpdate, out result).Data;
            T itmbk = itm.Clone();
            itm = item;
            try
            {
                if (isReadOnly)
                {
                    throw new IsReadOnlyException();
                }
                else
                {
                    if (result == false)
                    {
                        throw new ItemNotFoundException();
                    }

                    query = SqlUtils<T>.UpdateQueryFormater(item);

                    sqlCommand = new SqlCommand(query, sqlConnection);
                    sqlConnection.Open();
                    hasConnectionOpen = true;
                    sqlCommand.ExecuteNonQuery();
                    if (haveLog)
                    {
                        
                        Archive(query, Operations.Update,null);
                    }
                    if (hasConnectionOpen)
                    {
                        sqlConnection.Close();
                    }
                    Updated(this, new UpdatedEventArgs(itmbk, query, Operations.Update));
                    result = true;
                    return result;
                    
                }
            }
            catch (ItemNotFoundException ex)
            {
                if (result)
                {
                    itm = itmbk;
                }
                if (haveLog)
                {
                    Archive(query, Operations.Update, ex);
                }
                Failed(this, new FailedEventArgs(query, ex, Operations.Update));
                return result;
            }
            catch (CoreFaultException ex)
            {
                if (result)
                {
                    itm = itmbk;
                }
                if (haveLog)
                {
                    Archive(query, Operations.Update, ex);
                }
                Failed(this, new FailedEventArgs(query, ex, Operations.Update));
                return result;
            }
            catch (Exception ex)
            {
                if (result)
                {
                    itm = itmbk;
                }
                if (haveLog)
                {
                    Archive(query, Operations.Update, ex);
                }
                Failed(this, new FailedEventArgs(query, ex, Operations.Update));
                return result;
            }
            finally
            {
                if (hasConnectionOpen)
                {
                    sqlConnection.Close();
                    hasConnectionOpen = false;
                }
                if (CollectionChanged != null)
                {
                    CollectionChanged(this, new System.EventArgs());
                }
            }
        }

        private bool DeleteFromSQL(T itemToDelete)
        {
            bool b = false;
            string query = string.Empty;
            try
            {
                if (isReadOnly)
                {
                    throw new IsReadOnlyException();
                }
                else
                {
                    query = SqlUtils<T>.DeleteQueryFormater(itemToDelete);
                    sqlCommand = new SqlCommand(query, sqlConnection);
                    sqlConnection.Open();
                    hasConnectionOpen = true;
                    sqlCommand.ExecuteNonQuery();
                    if (hasConnectionOpen)
                    {
                        sqlConnection.Close();
                    }
                    Remove(beginNode, itemToDelete, null, b);
                    if (haveLog)
                    {
                        Archive(query, Operations.Delete, null);
                    }
                    Deleted(this, new DeletedEventArgs(itemToDelete, query, Operations.Delete));
                    if (CollectionChanged != null)
                    {
                        CollectionChanged(this, new System.EventArgs());
                    }
                    return true;
                }
            }
            catch (IsReadOnlyException ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.Delete, ex);
                }
                Failed(this, new FailedEventArgs(query, ex, Operations.Delete));
                return false;
            }
            catch (CoreFaultException ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.Delete, ex);
                }
                Failed(this, new FailedEventArgs(query, ex, Operations.Delete));
                return false;
            }
            catch (Exception ex)
            {
                if (haveLog)
                {
                    Archive(query, Operations.Delete, ex);
                }
                Failed(this, new FailedEventArgs(query, ex, Operations.Delete));
                return false;
            }
            finally
            {
                if (hasConnectionOpen)
                {
                    sqlConnection.Close();
                    hasConnectionOpen = false;
                }
                if (CollectionChanged != null)
                {
                    CollectionChanged(this, new System.EventArgs());
                }
            }
        }

        private void Archive(string query, Operations operation,Exception ex)
        {
            //string timeOcurrency = DateTime.Now.ToLongTimeString();
            //StringBuilder strBuilder = new StringBuilder();
            //strBuilder.Append(timeOcurrency);
            //strBuilder.Append(S_DELIMITER);
            //strBuilder.Append(query);
            //switch (operation)
            //{
            //    case Operations.Insert:
            //        strBuilder.Append(S_DELIMITER);
            //        strBuilder.Append(S_INSERTMESSAGE);
                    
            //        break;
            //    case Operations.Delete:
            //        strBuilder.Append(S_DELIMITER);
            //        strBuilder.Append(S_DELETEMESSAGE);
            //        break;
            //    case Operations.Select:
            //        strBuilder.Append(S_DELIMITER);
            //        strBuilder.Append(S_EXECUTEMESSAGE);
            //        break;
            //    case Operations.SelectAll:
            //        strBuilder.Append(S_DELIMITER);
            //        strBuilder.Append(S_EXECUTEMESSAGE);
            //        break;
            //    case Operations.Update:
            //        strBuilder.Append(S_DELIMITER);
            //        strBuilder.Append(S_UPDATEMESSAGE);
            //        break;
            //}
            //if (ex != null)
            //{
            //    strBuilder.Append(S_DELIMITER);
            //    strBuilder.Append(S_FAILEDMESSAGE);
            //}
            //LogUtils.RetrieveInformationToLog(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments).ToString(), filename, strBuilder.ToString());
            
            //Thread thread = new Thread();

        }        

        #endregion
        
        #region Full Recursive

        private Node<T> Do(Node<T> BeginNode)
        {
            if (BeginNode.Next != null)
            {
                return Do(BeginNode.Next);
            }
            else
            {
                return BeginNode;
            }
        }

        private Node<T> Exist(Node<T> BeginNode, T itemToCompare, out bool exists)
        {
            if (BeginNode.Data.Equals(itemToCompare))
            {
                exists = true;
                return BeginNode;
            }
            else
            {
                if (BeginNode.Next == null)
                {
                    exists = false;
                    return null;
                }
                else
                {
                    return Exist(BeginNode.Next, itemToCompare, out exists);
                }
            }
        }

        private Node<T> GetNodeFromIndex(Node<T> BeginNode, int count, int index)
        {
            int co = count;
            co = co + 1;
            if (BeginNode.Next != null)
            {
                if (count > index)
                {
                    throw new IndexOutOfRangeException("No se encontró el registro buscado", new CoreFaultException());
                }
                else
                {
                    if (count == index)
                    {
                        return BeginNode;
                    }
                    else
                    {
                        return GetNodeFromIndex(BeginNode.Next, co, index);
                    }
                }
            }
            else
            {
                return BeginNode;
            }
        }

        private void InsertIntoIndex(Node<T> BeginNode, Node<T> LastNode, T item, int count, int index)
        {
            if (index <= count)
            {
                if (BeginNode.Next != null)
                {
                    InsertIntoIndex(BeginNode.Next, BeginNode, item, count++, index);
                }
                else
                {
                    Node<T> tmp = new SmartContainer<T>.Node<T>();
                    tmp.Data = item;
                    tmp.Next = BeginNode.Next;
                    BeginNode.Next = tmp;
                    count++;
                }
            }
            else
            {
                throw new IndexOutOfRangeException();

            }
        }

        private int Counter(Node<T> BeginNode, int counter)
        {
            int i = counter;
            if (BeginNode.Next != null)
            {
                i=i+1;
                return Counter(BeginNode.Next, i);
            }
            else
            {
                return i;
            }
        }

        private List<T> AddToList(Node<T> BeginNode, List<T> list)
        {
            if (BeginNode.Next != null)
            {
                list.Add(BeginNode.Data);
                return AddToList(BeginNode.Next, list);
            }
            else
            {
                list.Add(BeginNode.Data);
                return list;
            }
        }

        private int FindIndex(Node<T> BeginNode, T itemSearch, int index)
        {
            if (BeginNode.Next != null)
            {
                return FindIndex(BeginNode.Next, itemSearch, index++);
            }
            else
            {
                return -1;
                throw new CoreFaultException();                
            }
        }

        private void RemoveFromIndex(Node<T> BeginNode, Node<T> LastNode, int count, int index)
        {
            if (index <= count)
            {
                if (BeginNode.Next != null)
                {
                    RemoveFromIndex(BeginNode.Next, BeginNode, count, index++);
                }
                else
                {
                    LastNode.Next = BeginNode.Next;
                    BeginNode.Next = null;
                }
            }
            else
            {
                throw new IndexOutOfRangeException();
            }
        }

        private bool Remove(Node<T> BeginNode, T item, Node<T> LastNode, bool deleted)
        {
            if (BeginNode.Next != null)
            {
                if (BeginNode.Data.Equals(item))
                {
                    LastNode.Next = BeginNode.Next;
                    beginNode.Next = null;
                    return true;
                }
                else
                {
                    return Remove(BeginNode.Next, item, BeginNode, deleted);
                }
            }
            else
            {
                return false;
            }
        }

        private void AddToContainerFromSQL(Node<T> BeginNode,Node<T> LastNode, Type classType, SqlDataReader reader)
        {
            if (reader.Read())
            {
                if (BeginNode != null)
                {
                    if (BeginNode.Data == null)
                    {
                        T tempData = Activator.CreateInstance<T>();
                        foreach (PropertyInfo property in classType.GetProperties())
                        {
                            if (property.Name == "PcUsuario")
                            {
                                property.SetValue(tempData, SecurityProgram.PcProfile, null);
                            }
                            else
                            {
                                if (property.Name == "FechaTransaccion")
                                {
                                    property.SetValue(tempData, DateTime.Now, null);
                                }
                                else
                                {
                                    if (property.Name == "Operacion")
                                    {
                                        property.SetValue(tempData, Operations.SelectAll, null);
                                    }
                                    else
                                    {
                                        property.SetValue(tempData, reader.GetValue(reader.GetOrdinal(property.Name)), null);
                                    }
                                }
                            }
                        }
                        BeginNode.Data = tempData;
                        BeginNode.Next = Activator.CreateInstance<Node<T>>();
                        AddToContainerFromSQL(BeginNode.Next,BeginNode, classType, reader);
                    }
                    else
                    {
                        BeginNode.Next = Activator.CreateInstance<Node<T>>();
                        AddToContainerFromSQL(BeginNode.Next,BeginNode, classType, reader);
                    }
                }
                else
                {
                    BeginNode = Activator.CreateInstance<Node<T>>();
                    T tempData = Activator.CreateInstance<T>();
                    foreach (PropertyInfo property in classType.GetProperties())
                    {
                        if (Attribute.GetCustomAttribute(property, typeof(Encrypted)) != null)
                        {
                            property.SetValue(tempData, SecurityUtils.Decrypt(reader.GetValue(reader.GetOrdinal(property.Name)).ToString()), null);
                        }
                        else
                        {
                            property.SetValue(tempData, reader.GetValue(reader.GetOrdinal(property.Name)), null);
                        }
                    }
                    BeginNode.Data = tempData;
                    BeginNode.Next = Activator.CreateInstance<Node<T>>();
                    AddToContainerFromSQL(BeginNode.Next,BeginNode, classType, reader);
                }
            }
            else
            {
                LastNode.Next = null;
            }
        }

        private List<T> ApplyAdvanceFilter(Node<T> BeginNode, Node<T> lastNode, Type classType, SqlDataReader reader, List<T> filtered,string query)
        {
            if (reader.Read())
            {
                if (BeginNode != null)
                {
                    if (BeginNode.Data == null)
                    {
                        T tempData = Activator.CreateInstance<T>();
                        foreach (PropertyInfo property in classType.GetProperties())
                        {
                            try
                            {
                                if (property.Name == "PcUsuario")
                                {
                                    property.SetValue(tempData, SecurityProgram.PcProfile, null);
                                }
                                else
                                {
                                    if (property.Name == "FechaTransaccion")
                                    {
                                        property.SetValue(tempData, DateTime.Now, null);
                                    }
                                    else
                                    {
                                        if (property.Name == "Operacion")
                                        {
                                            property.SetValue(tempData, Operations.Select, null);
                                        }
                                        else
                                        {
                                            property.SetValue(tempData, reader.GetValue(reader.GetOrdinal(property.Name)), null);
                                        }
                                    }
                                }
                            }
                            catch
                            {
                                property.SetValue(tempData, null, null);
                            }
                        }
                        BeginNode.Data = tempData;
                        filtered.Add(tempData);
                        BeginNode.Next = Activator.CreateInstance<Node<T>>();
                    }
                    else
                    {
                        BeginNode.Next = Activator.CreateInstance<Node<T>>();
                        T tempData = Activator.CreateInstance<T>();
                        foreach (PropertyInfo property in classType.GetProperties())
                        {
                            if (property.Name == "PcUsuario")
                            {
                                property.SetValue(tempData, SecurityProgram.PcProfile, null);
                            }
                            else
                            {
                                if (property.Name == "FechaTransaccion")
                                {
                                    property.SetValue(tempData, DateTime.Now, null);
                                }
                                else
                                {
                                    if (property.Name == "Operacion")
                                    {
                                        property.SetValue(tempData, Operations.Select, null);
                                    }
                                    else
                                    {
                                        property.SetValue(tempData, reader.GetValue(reader.GetOrdinal(property.Name)), null);
                                    }
                                }
                            }
                        }
                        filtered.Add(tempData);
                        BeginNode.Data = tempData;
                        BeginNode.Next = Activator.CreateInstance<Node<T>>();
                    }
                    
                }
                return ApplyAdvanceFilter(BeginNode.Next, BeginNode, classType, reader, filtered, query);
            }
            else
            {
                if (lastNode == null)
                {
                    return null;
                }
                else
                {
                    lastNode.Next = null;
                    if (haveLog)
                    {
                        Archive(query, Operations.Select, null);
                    }
                    return filtered;
                }
            }
        }

        #endregion      

        #endregion        

        #region Public Methods

        /// <summary>
        /// Get the index of the item you are looking for.
        /// </summary>
        /// <param name="item">
        /// Item you are looking for.
        /// </param>
        /// <returns>
        /// Number of the index if found, else you get -1.
        /// </returns>
        public int IndexOf(T item)
        {
            return FindIndex(beginNode, item, 0);
        }

        /// <summary>
        /// Insert into the referred index, but not on the table on SQL.
        /// </summary>
        /// <param name="index">
        /// The position where the item will be.
        /// </param>
        /// <param name="item">
        /// The item that be inserted
        /// </param>
        public void Insert(int index, T item)
        {
            InsertIntoIndex(beginNode, null, item, 0, index);
        }

        /// <summary>
        /// Remove the item in the position of the index
        /// </summary>
        /// <param name="index">
        /// Value of the position.
        /// </param>
        public void RemoveAt(int index)
        {
            RemoveFromIndex(beginNode, null, 0, index);
        }

        /// <summary>
        /// Refill the collection with last changes on the database.
        /// </summary>
        public void Refresh()
        {
            bool s = this.SelectAllFromSQL();
        }

        /// <summary>
        /// Add the item into database
        /// </summary>
        /// <param name="item">
        /// Item to add.
        /// </param>
        public void Add(T item)
        {
            bool b = this.AddToSQL(beginNode, item);
            if (b)
            {
                if (CollectionChanged != null)
                {
                    CollectionChanged(this, new System.EventArgs());
                }
            }
        }

        /// <summary>
        /// Update an existing elemnt in the database.
        /// </summary>
        /// <param name="itemToUpdate">
        /// The item you want to update.
        /// </param>
        /// <param name="item">
        /// The item with changes.
        /// </param>
        public void Update(T itemToUpdate, T item)
        {
            bool b = this.UpdateToSQL(itemToUpdate, item);
            if (b)
            {
                if (CollectionChanged != null)
                {
                    CollectionChanged(this, new System.EventArgs());
                }
            }
        }

        /// <summary>
        /// The content of container is empty.
        /// </summary>
        public void Clear()
        {
            beginNode.Data = default(T);
            beginNode.Next = null;
        }

        /// <summary>
        /// Method that search if the item exists on the collection.
        /// </summary>
        /// <param name="item">
        /// The item you are looking for.
        /// </param>
        /// <returns>
        /// True if exists.
        /// </returns>
        public bool Contains(T item)
        {
            bool exist = false;
            Node<T> temp = Exist(beginNode, item, out exist);
            return exist;
        }

        public void CopyTo(T[] array, int arrayIndex)
        {

        }

        /// <summary>
        /// Fill this collection with all the items on the database.
        /// </summary>
        public void SelectAll()
        {
            this.SelectAllFromSQL();
            count = this.Counter(beginNode, count)+1;
        }

        /// <summary>
        /// Delete the item in the database.
        /// </summary>
        /// <param name="item">
        /// Item to delete.
        /// </param>
        /// <returns>
        /// True if deleted.
        /// </returns>
        public bool Remove(T item)
        {
            return this.DeleteFromSQL(item);
        }

        /// <summary>
        /// Method to enumerate
        /// </summary>
        /// <returns></returns>
        public IEnumerator<T> GetEnumerator()
        {
            this.Reset();
            
            while (this.MoveNext())
            {
                yield return current;
            }
            
        }        

        #endregion

        #region Override Methods

        /// <summary>
        /// Compare the actual item with another.
        /// </summary>
        /// <param name="obj">
        /// The item to compare.
        /// </param>
        /// <returns>
        /// True if equals, else false.
        /// </returns>
        public override bool Equals(object obj)
        {
            return GetNodeFromIndex(beginNode, 0, index).Equals(obj);
        }

        /// <summary>
        /// Convert the current item to a string.
        /// </summary>
        /// <returns>
        /// Returns the content string.
        /// </returns>
        public override string ToString()
        {
            return GetNodeFromIndex(beginNode, 0, index).Data.ToString();
        }

        /// <summary>
        /// Get the HashCode of the current item.
        /// </summary>
        /// <returns>
        /// Int of HashCode.
        /// </returns>
        public override int GetHashCode()
        {
            return GetNodeFromIndex(beginNode, 0, index).GetHashCode();
        }

        #endregion

        #region Miembros de IEnumerable<T>

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return this;
        }

        #endregion
        
        #region Miembros de IDisposable

        /// <summary>
        /// Remove the collection on memory.
        /// </summary>
        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        #endregion

        #region Miembros de IEnumerator<T>

        /// <summary>
        /// Get the current item.
        /// </summary>
        public T Current
        {
            get 
            {
                return current;
            }
        }

        #endregion

        #region Miembros de IEnumerator

        /// <summary>
        /// Get the enumerator of the collection.
        /// </summary>
        object System.Collections.IEnumerator.Current
        {
            get 
            { 
                return current; 
            }
        }

        /// <summary>
        /// Pass to the next item in the collection.
        /// </summary>
        /// <returns>
        /// Get false if there is no next item, else true.
        /// </returns>
        public bool MoveNext()
        {
            index++;
            if (index >= this.count)
            {
                return false;
            }
            else
            {
                current = this.GetNodeFromIndex(beginNode, 0, index).Data;
                return true;
            }
        }

        /// <summary>
        /// Restart the index of collection
        /// </summary>
        public void Reset()
        {
            index = -1;
        }

        #endregion

    }
}